mysql 分组排序 取前top n |
您所在的位置：网站首页 › mysql 取第一条数据 › mysql 分组排序 取前top n |
mysql 分组排序 取前top n
|
力扣mysql 题目为：
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department 表包含公司所有部门的信息。 +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+ 编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回： +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ 解释： IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。 来源：力扣（LeetCode） 链接：https://leetcode-cn.com/problems/department-top-three-salaries 解题思路 1 使用开窗函数 SELECT Department.NAME AS 'Department', a.NAME AS 'Employee', a.Salary FROM ( SELECT NAME, Salary, DepartmentId, DENSE_RANK() over ( PARTITION BY departmentid ORDER BY salary DESC ) AS rrank FROM Employee ) a JOIN Department WHERE Department.id = a.DepartmentId AND a.rrank (SELECT count( DISTINCT e2.Salary ) FROM Employee AS e2 WHERE e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId ) ORDER BY Department.NAME,Salary DESC;有的小伙伴可能看不明白上面的sql ，别着急 这里需要解释一下原生sql如何实现分组求top n ，上述题目的简化版， 引用一下别人的案例，可以放到数据库自己测试： DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `empno` decimal(4, 0) NOT NULL, `ename` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `job` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `mgr` decimal(4, 0) NULL DEFAULT NULL, `hiredate` datetime(0) NULL DEFAULT NULL, `sal` decimal(7, 2) NULL DEFAULT NULL, `comm` decimal(7, 2) NULL DEFAULT NULL, `deptno` decimal(2, 0) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of emp -- ---------------------------- INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20); INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30); INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30); INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20); INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30); INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30); INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10); INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.00, NULL, 20); INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10); INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30); INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.00, NULL, 20); INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30); INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, NULL, 20); INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10); SET FOREIGN_KEY_CHECKS = 1;导入数据得到一下表数据
每个部门对应top3 sql SELECT * FROM emp e WHERE ( SELECT count( 1 ) FROM emp WHERE deptno = e.deptno AND e.sal < sal ) < 3 ORDER BY deptno, sal
拿deptno 为30举例 sal 的值对应[1600.00,1250.00,1250.00,2850.00,1500.00,950.00] ,e.sal依次遍历 sal 也会依次遍历 当e.sal=1600.00时， e.sal |
【本文地址】
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |